﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Public;
using System.Data;

namespace DAL
{
    public class ChuyenKho_DAL
    {
        Connection conn = new Connection();
        ChuyenKho_PB ck_pb = new ChuyenKho_PB();

        #region Lấy ds phiếu chuyển
        public DataTable LayDSPhieuChuyen()
        {
            return conn.LoadData(" select ck.*,nv.TENNHANVIEN from CHUYENKHO ck,NHANVIEN nv where ck.NGUOIVANCHUYEN=nv.MANHANVIEN");
        }
        #endregion

        #region Tim theo ma phiếu chuyển kho
        public DataTable TimMaPhieuChuyenKho(string ma)
        {
            string sql = string.Format(" SELECT ctk.*,vt.TENVATTU,dvt.DIENGIAI,kh1.TENKHO as NHAPKHO,kh2.TENKHO as XUATKHO FROM CHITIETCHUYENKHO ctk,VATTU vt,DONVITINH dvt,KHO kh1,KHO kh2 where ctk.MAHANG=vt.MAVATTU and dvt.MADONVITINH=vt.DONVITINH and kh1.MAKHO=ctk.NHAPTAIKHO and kh2.MAKHO=ctk.XUATTAIKHO and ctk.MACHUYENKHO='{0}'", ma);
            return conn.LoadData(sql);
        }
        #endregion

        #region Lấy ds nhân viên
        public DataTable LayDSNV()
        {
            return conn.LoadData("select MANHANVIEN as [Mã NV],TENNHANVIEN as [Tên NV] from NHANVIEN");
        }
        #endregion

        #region "Lấy DS vật tư"
        public DataTable LayDSVatTu()
        {
            return conn.LoadData("SELECT VATTU.MAVATTU, VATTU.TENVATTU,VATTU.DONGIAMUA, DONVITINH.DIENGIAI FROM VATTU INNER JOIN DONVITINH ON VATTU.DONVITINH = DONVITINH.MADONVITINH");
        }
        #endregion

        #region "Lấy DS Kho"
        public DataTable LayDSKho()
        {
            return conn.LoadData("select MAKHO,TENKHO from KHO");
        }
        #endregion

        #region Lấy ds chi tiết chuyển kho
        public DataTable LayDSCTChuyenKho()
        {
            string sql = " SELECT ctk.*,vt.TENVATTU,dvt.DIENGIAI,kh1.TENKHO as NHAPKHO,kh2.TENKHO as XUATKHO FROM CHITIETCHUYENKHO ctk,VATTU vt,DONVITINH dvt,KHO kh1,KHO kh2 where ctk.MAHANG=vt.MAVATTU and dvt.MADONVITINH=vt.DONVITINH and kh1.MAKHO=ctk.NHAPTAIKHO and kh2.MAKHO=ctk.XUATTAIKHO and ctk.MACHUYENKHO='XXXX'";
            return conn.LoadData(sql);
        }
        #endregion

        #region "Tăng mã tự động phiếu nhập"
        public string NextID_CK()
        {
            return NextID_PB.NextID(conn.GetLastID("CHUYENKHO", "MACHUYENKHO"), "CK");
        }
        #endregion

        #region "Tăng mã tự động chi tiết chuyển"
        public string NextID_CTC()
        {
            return NextID_PB.NextID(conn.GetLastID("CHITIETCHUYENKHO", "ID"), "CTC");
        }
        #endregion

        #region "Thêm phiếu chuyển kho"
        public int ThemChuyenKho(ChuyenKho_PB ck_pb)
        {
            int nparameter = 5;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@machuyenkho"; value[0] = ChuyenKho_PB.MaChuyenKho;
            name[1] = "@diengiai"; value[1] = ChuyenKho_PB.DienGiai;
            name[2] = "@nguoivanchuyen"; value[2] = ChuyenKho_PB.NguoiVanChuyen;
            name[3] = "@ngayhachtoan"; value[3] = ChuyenKho_PB.NgayHachToan;
            name[4] = "@ngaychungtu"; value[4] = ChuyenKho_PB.NgayChungTu;
            string sql = "insert into CHUYENKHO values (@machuyenkho,@diengiai,@nguoivanchuyen,@ngayhachtoan,@ngaychungtu)";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region "Sửa phiếu chuyển kho"
        public int SuaChuyenKho(ChuyenKho_PB ck_pb)
        {
            int nparameter = 5;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@machuyenkho"; value[0] = ChuyenKho_PB.MaChuyenKho;
            name[1] = "@diengiai"; value[1] = ChuyenKho_PB.DienGiai;
            name[2] = "@nguoivanchuyen"; value[2] = ChuyenKho_PB.NguoiVanChuyen;
            name[3] = "@ngayhachtoan"; value[3] = ChuyenKho_PB.NgayHachToan;
            name[4] = "@ngaychungtu"; value[4] = ChuyenKho_PB.NgayChungTu;
            string sql = "update CHUYENKHO set DIENGIAI=@diengiai,NGUOIVANCHUYEN=@nguoivanchuyen,NGAYHACHTOAN=@ngayhachtoan,NGAYCHUNGTU=@ngaychungtu where MACHUYENKHO=@machuyenkho";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region "Thêm CT chuyển Kho"
        public int ThemCTChuyenKho(ChuyenKho_PB ck_pb)
        {
            int nparameter = 8;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@id"; value[0] = ChuyenKho_PB.ID;
            name[1] = "@mapc"; value[1] = ChuyenKho_PB.MaChuyenKho;
            name[2] = "@mahang"; value[2] = ChuyenKho_PB.MaHang;
            name[3] = "@xuattaikho"; value[3] = ChuyenKho_PB.XuatTaiKho;
            name[4] = "@nhaptaikho"; value[4] = ChuyenKho_PB.NhapTaiKho;
            name[5] = "@soluong"; value[5] = ChuyenKho_PB.SoLuong;
            name[6] = "@thanhtien"; value[6] = ChuyenKho_PB.ThanhTien;
            name[7] = "@dongia"; value[7] = ChuyenKho_PB.DonGia;
            string sql = "insert into CHITIETCHUYENKHO values (@id,@mapc,@mahang,@xuattaikho,@nhaptaikho,@soluong,@dongia,@thanhtien)";

            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region "Sửa CT chuyển kho"
        public int SuaCTChuyenKho(ChuyenKho_PB ck_pb)
        {
            int nparameter = 8;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@id"; value[0] = ChuyenKho_PB.ID;
            name[1] = "@mapc"; value[1] = ChuyenKho_PB.MaChuyenKho;
            name[2] = "@mahang"; value[2] = ChuyenKho_PB.MaHang;
            name[3] = "@xuattaikho"; value[3] = ChuyenKho_PB.XuatTaiKho;
            name[4] = "@nhaptaikho"; value[4] = ChuyenKho_PB.NhapTaiKho;
            name[5] = "@soluong"; value[5] = ChuyenKho_PB.SoLuong;
            name[6] = "@thanhtien"; value[6] = ChuyenKho_PB.ThanhTien;
            name[7] = "@dongia"; value[7] = ChuyenKho_PB.DonGia;

            string sql = "update CHITIETCHUYENKHO set MAHANG=@mahang,XUATTAIKHO=@xuattaikho,NHAPTAIKHO=@nhaptaikho,SOLUONG=@soluong,DONGIA=@dongia,THANHTIEN=@thanhtien where ID=@id";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region "Xóa CT chuyển kho"
        public int XoaCTChuyenKho(ChuyenKho_PB ck_pb)
        {
            int nparameter = 1;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@id"; value[0] = ChuyenKho_PB.ID;
            string sql = "delete from CHITIETCHUYENKHO where ID=@id";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region "Tìm theo ID Chi tiết chuyển kho"
        public DataTable TimID(ChuyenKho_PB ck_pb)
        {
            string sql = string.Format("select * from CHITIETCHUYENKHO where MACHUYENKHO='{0}'", ChuyenKho_PB.MaChuyenKho);
            return conn.LoadData(sql);
        }
        #endregion

        #region "Xóa phiếu chuyển kho"
        public int XoaChuyenKho(ChuyenKho_PB ck_pb)
        {
            int nparameter = 1;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@mapc"; value[0] = ChuyenKho_PB.MaChuyenKho;
            string sql = " delete from CHUYENKHO where MACHUYENKHO=@mapc";
            return conn.Update(sql, name, value, nparameter);
        }
        public int XoaChuyenKho1(ChuyenKho_PB ck_pb)
        {
            int nparameter = 1;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@mapc"; value[0] = ChuyenKho_PB.MaChuyenKho;
            string sql = " delete from CHITIETCHUYENKHO where MACHUYENKHO=@mapc";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region Tim theo mã kho
        public DataTable TimMaKho(string mavattu)
        {
            string sql = "select vt.MAKHO,kh.TENKHO from VATTU vt,KHO kh where MAVATTU='" + mavattu + "' and vt.MAKHO=kh.MAKHO";
            return conn.LoadData(sql);
        }
        #endregion

        #region Thêm
        public int ThemTonKho(ChuyenKho_PB ck_pb)
        {
            int nparameter = 4;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@mahang"; value[0] = ChuyenKho_PB.MaHang;
            name[1] = "@kho"; value[1] = ChuyenKho_PB.NhapTaiKho;
            name[2] = "@soluong"; value[2] = ChuyenKho_PB.SoLuong;
            name[3] = "@matk"; value[3] = ChuyenKho_PB.MaTK;
            string sql = "insert into TONKHO values(@matk,@mahang,@kho,@soluong,null,null)";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region Sửa tồn kho xuất
        public int SuaTonKhoXuat(ChuyenKho_PB ck_pb)
        {
            int nparameter = 3;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@mahang"; value[0] = ChuyenKho_PB.MaHang;
            name[1] = "@kho"; value[1] = ChuyenKho_PB.XuatTaiKho;
            name[2] = "@soluong"; value[2] = ChuyenKho_PB.SoLuong;
            string sql = "update TONKHO set SOLUONG=@soluong where MAVATTU=@mahang and KHO=@kho";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region Sửa tồn kho nhập
        public int SuaTonKhoNhap(ChuyenKho_PB ck_pb)
        {
            int nparameter = 3;
            string[] name = new string[nparameter];
            object[] value = new object[nparameter];
            name[0] = "@mahang"; value[0] = ChuyenKho_PB.MaHang;
            name[1] = "@kho"; value[1] = ChuyenKho_PB.NhapTaiKho;
            name[2] = "@soluong"; value[2] = ChuyenKho_PB.SoLuong;
            string sql = "update TONKHO set SOLUONG=@soluong where MAVATTU=@mahang and KHO=@kho";
            return conn.Update(sql, name, value, nparameter);
        }
        #endregion

        #region Kiểm tra mã hàng cả kho đó có trong bảng tôn kho chưa
        //kiem tra ton tai
        public bool CheckExists(string mavattu, string makho)
        {
            if ((conn.CheckExistValues("TONKHO", "MAVATTU", mavattu, makho)))
                return true;
            return false;
        }
        #endregion

        #region Lấy số lượng tồn kho
        public int LaySLTonKho(string mavattu, string makho)
        {
            string sql = string.Format("select * from TONKHO where MAVATTU='{0}' and KHO='{1}'", mavattu, makho);
            return conn.LaySLTonKho(sql);
        }
        #endregion

        #region Tìm mã tồn kho
        public DataTable TimMaTonKho(string mavattu, string makho)
        {
            return conn.LoadData("select MATK from TONKHO where MAVATTU='" + mavattu + "' and KHO='" + makho + "'");
        }
        #endregion

        #region "Tăng mã tự động tồn kho"
        public string NextID_TK()
        {
            return NextID_PB.NextID(conn.GetLastID("TONKHO", "MATK"), "TK");
        }
        #endregion
    }
}
